package org.jeecg.modules.iot.middleware.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import org.jeecg.modules.iot.model.CarLocationResp;
import org.jeecg.modules.iot.model.Location;
import org.jeecg.modules.iot.model.LocationJson;
import org.jeecg.modules.iot.middleware.service.ILocationService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

@Service
@DS("tdengine")
public class LocationServiceImpl implements ILocationService {

    @Autowired
    private JdbcTemplate jdbcTemplate;





    public int createTable(String tbName, String location, int groupId){
        return jdbcTemplate.update(
                "CREATE TABLE "+tbName+" USING location TAGS (?);"
                , 1);
    }

    /*暂时把t+手机号做成表名*/
    @Override
    public int add(Location one) {
        return jdbcTemplate.update(
                "INSERT INTO "+ one.getTableName() +" USING location TAGS ( "+one.getDeviceId()+" ) (ts,latitude,longitude,speed,alarm,status_field,elevation,direction,mileage,rMileage,sMileage,dMileage,fMileage,eConsum,sConsum,snnNumber,seq) VALUES (?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?)",

                one.getTs(), one.getLatitude(),one.getLongitude(),one.getSpeed(),one.getAlarm(),one.getStatusField(),one.getElevation(),one.getDirection(),one.getMileage(),one.getRMileage(),one.getSMileage(),one.getDMileage(),one.getFMileage(),one.getEConsum(),one.getSConsum(),one.getSnnNumber(),one.getSeq()
        );
    }
    @Override
    public Location queryBySeq(Integer seq, String deviceId){

        try {


        return  jdbcTemplate.queryForObject("select  `ts`, `latitude`, `longitude`,`speed`,`seq`,`alarm`,`status_field`,`elevation` ,`snnnumber`  from ?  where seq = ? order by ts desc limit 1",

        (rs, rowNum) -> {
            Timestamp ts = rs.getTimestamp("ts");
            Double latitude = rs.getDouble("latitude");
            Double longitude = rs.getDouble("longitude");
            short speed = rs.getShort("speed");
            String seqNum = rs.getString("seq");
            Integer alarm = rs.getInt("alarm");
            Integer statusField = rs.getInt("status_field");
            short elevation = rs.getShort("elevation");
            short direction = rs.getShort("direction");
            return new Location( ts,alarm,latitude,statusField,longitude,elevation,speed,direction,seqNum,deviceId);
        },"t"+deviceId,seq);
        } catch (EmptyResultDataAccessException e) {
            // 如果查询不到数据, 可以返回 null 或者抛出自定义异常
            return null;
        }catch (Exception e){
            throw new RuntimeException("Error querying data by sequence: " + seq, e);
        }

    }

    @Override
    public Location queryByDeviceId(String deviceId) {
        try {

            String sql = "SELECT * FROM location WHERE device_id = ?   order by ts desc limit 1";
            return jdbcTemplate.queryForObject(sql, new Object[]{deviceId}, new BeanPropertyRowMapper<>(Location.class));
        } catch (EmptyResultDataAccessException e) {
            // 处理没有结果的情况
            return null;
        } catch (DataAccessException e) {
            // 处理其他数据库访问异常
            throw new RuntimeException("Error querying data by sequence: " + deviceId, e);
        }
    }

    @Override
    public Location queryStaticByDeviceId(String deviceId) {
        try {

            String sql = "SELECT * FROM location WHERE device_id = ?  and speed=0 order by ts desc limit 1";
            return jdbcTemplate.queryForObject(sql, new Object[]{deviceId}, new BeanPropertyRowMapper<>(Location.class));
        } catch (EmptyResultDataAccessException e) {
            // 处理没有结果的情况
            return null;
        } catch (DataAccessException e) {
            // 处理其他数据库访问异常
            throw new RuntimeException("Error querying data by sequence: " + deviceId, e);
        }
    }

    @Override
    public Location queryMoveByDeviceId(String deviceId) {
        try {

            String sql = "SELECT * FROM location WHERE device_id = ?  and speed!=0 order by ts desc limit 1";
            return jdbcTemplate.queryForObject(sql, new Object[]{deviceId}, new BeanPropertyRowMapper<>(Location.class));
        } catch (EmptyResultDataAccessException e) {
            // 处理没有结果的情况
            return null;
        } catch (DataAccessException e) {
            // 处理其他数据库访问异常
            throw new RuntimeException("Error querying data by sequence: " + deviceId, e);
        }

    }
    @Override
    public List<LocationJson> queryLocationsByTimeRange(String deviceId, Timestamp startTime, Timestamp endTime) {
        String tableName = "t" + deviceId;
        String sql = "SELECT ts, latitude, longitude,speed,mileage " +
                "FROM " + tableName + " " +
                "WHERE ts BETWEEN ? AND ? " +
                "ORDER BY ts ASC";

        try {
            return jdbcTemplate.query(sql, new Object[]{startTime, endTime},
                    (rs, rowNum) -> {
                        Timestamp ts = rs.getTimestamp("ts");
                        Double latitude = rs.getDouble("latitude");
                        Double longitude = rs.getDouble("longitude");
                        int speed = rs.getInt("speed");
                        // 获取里程数，处理空值并转换为千米
                        Float mileage = rs.getFloat("mileage");
                        float mileageKm = (mileage != null) ? mileage / 1000 : 0; // 米转换为千米

                        double[] gcj02 = gpsToGcj02(latitude, longitude);
                        return new LocationJson(gcj02[0], gcj02[1], ts, speed, (float) (Math.round(mileageKm * 100) / 100.0)); // 保留两位小数
                    });
        } catch (EmptyResultDataAccessException e) {
            // 如果查询不到数据, 可以返回空列表
            return new ArrayList<>();
        } catch (Exception e) {
            // 打印详细的错误信息以便调试
            System.err.println("SQL query: " + sql);
            System.err.println("Parameters: startTime=" + startTime + ", endTime=" + endTime);
            throw new RuntimeException("Error querying locations by time range for device: " + deviceId, e);
        }
    }

    @Override
    public Location queryOneKmBefore(String deviceId, Float milOne) {
        try {
            String sql = " SELECT last_row(device_id) device_id , last_row(latitude) latitude , last_row(longitude) longitude, last_row(ts) ts ,last_row(mileage) mileage,last_row(rMileage) rMileage ,last_row(sMileage) sMileage ,last_row(dMileage) dMileage  ,last_row(fMileage) fMileage  FROM location WHERE device_id = ? AND mileage <= ?  ";
            return jdbcTemplate.queryForObject(sql, new Object[]{deviceId, milOne}, new BeanPropertyRowMapper<>(Location.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        } catch (DataAccessException e) {
            throw new RuntimeException("Error querying one kilometer before data: " + deviceId, e);
        }
    }
    @Override
    public Location queryNearestLocationAfterTime(String deviceId, Timestamp timestamp) {
        try {
            String sql = "SELECT FIRST(ts) ts, FIRST(latitude) latitude, FIRST(longitude) longitude, FIRST(speed) speed, " +
                    "FIRST(alarm) alarm, FIRST(status_field) status_field, FIRST(elevation) elevation, " +
                    "FIRST(direction) direction, FIRST(mileage) mileage, FIRST(device_id) device_id, FIRST(snnnumber) snnNumber " +
                    "FROM location WHERE device_id = ? AND ts > ?";

            return jdbcTemplate.queryForObject(
                    sql,
                    new BeanPropertyRowMapper<>(Location.class),
                    deviceId, timestamp
            );
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
    @Override
    public Location queryNearestLocationBeforeTime(String deviceId, Timestamp timestamp) {
        try {
            String sql = "SELECT LAST_ROW(ts) ts, LAST_ROW(latitude) latitude, LAST_ROW(longitude) longitude, LAST_ROW(speed) speed, " +
                    "LAST_ROW(alarm) alarm, LAST_ROW(status_field) status_field, LAST_ROW(elevation) elevation, " +
                    "LAST_ROW(direction) direction, LAST_ROW(mileage) mileage,LAST_ROW(snnnumber) snnNumber, LAST_ROW(device_id) device_id " +
                    "FROM location WHERE device_id = ? AND ts < ? LIMIT 1";

            return jdbcTemplate.queryForObject(
                    sql,
                    new BeanPropertyRowMapper<>(Location.class),
                    deviceId, timestamp
            );
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }
    @Override
    public List<CarLocationResp> queryNearestDevices(double currentLatitude, double currentLongitude, Integer range ) {
        // 计算纬度和经度的范围
        double latRange = range / 111.0; // 1度纬度大约等于111公里
        double lonRange = range / (111.0 * Math.cos(Math.toRadians(currentLatitude))); // 1度经度的距离随纬度变化

        String sql = "SELECT device_id carCode,last_row(latitude) latitude , last_row(longitude) longitude, last_row(ts) ts " +
                "FROM location " +
                "WHERE latitude BETWEEN ? AND ? " +
                "AND longitude BETWEEN ? AND ? " +
                "GROUP BY device_id";

        List<CarLocationResp> locations = jdbcTemplate.query(sql, new Object[]{
                currentLatitude - latRange, currentLatitude + latRange,
                currentLongitude - lonRange, currentLongitude + lonRange
        }, new BeanPropertyRowMapper<>(CarLocationResp.class));

        return locations.stream()
                .filter(location -> {
                    double distance = calculateDistance(currentLatitude, currentLongitude, location.getLatitude(), location.getLongitude());
                    return distance < range;
                })
                .sorted((loc1, loc2) -> Double.compare(
                        calculateDistance(currentLatitude, currentLongitude, loc1.getLatitude(), loc1.getLongitude()),
                        calculateDistance(currentLatitude, currentLongitude, loc2.getLatitude(), loc2.getLongitude())
                ))
                .collect(Collectors.toList());
    }



    private double calculateDistance(double lat1, double lon1, double lat2, double lon2) {
        final int R = 6371; // Radius of the earth in km

        double latDistance = Math.toRadians(lat2 - lat1);
        double lonDistance = Math.toRadians(lon2 - lon1);
        double a = Math.sin(latDistance / 2) * Math.sin(latDistance / 2)
                + Math.cos(Math.toRadians(lat1)) * Math.cos(Math.toRadians(lat2))
                * Math.sin(lonDistance / 2) * Math.sin(lonDistance / 2);
        double c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        double distance = R * c; // convert to kilometers

        return distance;
    }

    public static double[] gpsToGcj02(double lat, double lon) {
        double dLat = transformLat(lon - 105.0, lat - 35.0);
        double dLon = transformLon(lon - 105.0, lat - 35.0);
        double radLat = lat / 180.0 * Math.PI;
        double magic = Math.sin(radLat);
        magic = 1 - 0.00669342162296594323 * magic * magic;
        double sqrtMagic = Math.sqrt(magic);
        dLat = (dLat * 180.0) / ((6335552.717000426 * sqrtMagic) * Math.PI);
        dLon = (dLon * 180.0) / ((6378245.0 / sqrtMagic * Math.cos(radLat)) * Math.PI);
        double mgLat = lat + dLat;
        double mgLon = lon + dLon;
        // 限制经纬度长度到小数点后 7 位
        mgLat = round(mgLat, 7);
        mgLon = round(mgLon, 7);

        return new double[]{mgLat, mgLon};
    }


    private static double round(double value, int places) {
        if (places < 0) throw new IllegalArgumentException();
        BigDecimal bd = new BigDecimal(Double.toString(value));
        bd = bd.setScale(places, RoundingMode.HALF_UP);
        return bd.doubleValue();
    }
    private static double transformLat(double x, double y) {
        double ret = -100.0 + 2.0 * x + 3.0 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * Math.sqrt(Math.abs(x));
        ret += (20.0 * Math.sin(6.0 * x * Math.PI) + 20.0 * Math.sin(2.0 * x * Math.PI)) * 2.0 / 3.0;
        ret += (20.0 * Math.sin(y * Math.PI) + 40.0 * Math.sin(y / 3.0 * Math.PI)) * 2.0 / 3.0;
        ret += (160.0 * Math.sin(y / 12.0 * Math.PI) + 320 * Math.sin(y * Math.PI / 30.0)) * 2.0 / 3.0;
        return ret;
    }

    private static double transformLon(double x, double y) {
        double ret = 300.0 + x + 2.0 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * Math.sqrt(Math.abs(x));
        ret += (20.0 * Math.sin(6.0 * x * Math.PI) + 20.0 * Math.sin(2.0 * x * Math.PI)) * 2.0 / 3.0;
        ret += (20.0 * Math.sin(x * Math.PI) + 40.0 * Math.sin(x / 3.0 * Math.PI)) * 2.0 / 3.0;
        ret += (150.0 * Math.sin(x / 12.0 * Math.PI) + 300.0 * Math.sin(x / 30.0 * Math.PI)) * 2.0 / 3.0;
        return ret;
    }




}
